import pandas as pd

# 读取Excel文件
excel_file = '终数据.xlsx'  # 替换为你的Excel文件路径
df = pd.read_excel(excel_file)

# 打印前几行数据以验证读取是否正确
print("原始数据:")
print(df.head())

# 定义加权评分函数
def calculate_weighted_score(distance, route_count, avg_distance,
                             distance_weight=0.8, route_count_weight=0.2):
    # 使用距离的倒数作为分数，与最短距离成反比
    distance_score = (1 / distance) * distance_weight
    # 使用路径数目作为分数，与路径数目成正比
    route_count_score = route_count * route_count_weight
    # 考虑平均最短距离的影响，距离越短分数越高
    average_distance_score = (1 / avg_distance) * distance_weight
    # 总分数
    total_score = distance_score + route_count_score + average_distance_score
    return total_score

# 计算每个起点的平均最短距离
df['average_distance'] = df.groupby('起点')['最短距离'].transform('mean')

# 避免除以零的错误处理
df['最短距离'] = df['最短距离'].replace(0, 1)  # 将0距离替换为1，以避免分母为零

# 应用加权评分函数到每一行数据
df['score'] = df.apply(lambda row: calculate_weighted_score(
    row['最短距离'], row['行车方案数'], row['average_distance']),
    axis=1)

# 显示添加了分数的新DataFrame
print("\n添加分数后的数据:")
print(df.head())

# 保存新的DataFrame到新的Excel文件
output_excel_file = 'output_excel_file_with_scores.xlsx'  # 替换为你希望保存的文件路径
df.to_excel(output_excel_file, index=False)
print(f"\n加权评分已完成，结果已保存到 {output_excel_file}")


